--CREATE DATABASE OPENXMLTesting
--GO


declare @FileLocation nvarchar(100)
declare @Execution nvarchar(1000)

set @filelocation = N'C:\rsreportserver.config'



CREATE TABLE #XMLwithOpenXML
(
Id INT IDENTITY PRIMARY KEY,
XMLData XML,
LoadedDateTime DATETIME
)



/* 
Testing

truncate table XMLwithOpenXML
drop table #XMLwithOpenXML
*/


set @Execution = N'INSERT INTO #XMLwithOpenXML(XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE() 
FROM OPENROWSET(BULK ''' + @filelocation + N''', SINGLE_BLOB) AS x;'



select @Execution
exec sp_executesql @Execution
SELECT * FROM #XMLwithOpenXML






DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)


SELECT @XML = XMLData FROM #XMLwithOpenXML


-- Get SSRS Configuration Settings for Connection to the Report server database
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML



select [Key], Value
from openxml(@hDoc, '/Configuration/Add',1)
WITH ([Key] varchar(1000), Value varchar(1000))


SELECT ConnectionType, LogonUser, LogonDomain, LogonCred
FROM OPENXML(@hDoc, 'Configuration')
WITH 
(
ConnectionType [varchar](100) 'ConnectionType'
,LogonUser  [varchar](100) 'LogonUser'
, LogonDomain  [varchar](100) 'LogonDomain'
, LogonCred  [varchar](100) 'LogonCred'
)

-- SELECT statement that uses the OPENXML rowset provider.


SELECT *
FROM OPENXML(@hDoc, 'Configuration/URLReservations/Application/Urls/URL/AccountName', 2)
WITH 
(
Name [varchar](100) '../../Name'
,VirtualDirectory [varchar](100) '../../VirtualDirectory'
,URL[varchar](100) '../UrlString'
,AccountName [varchar](100) 'AccountName'
)

select [Name], [Type]
from openxml(@hDoc, 'Configuration/Extensions/Render/Extension',1)
WITH ([Name] varchar(1000), [Type] varchar(1000))



SELECT MemoryThreshold,  MemorySafetyMargin,WorkingSetMaximum, WorkingSetMinimum
FROM OPENXML(@hDoc, 'Configuration/Service')
WITH 
(
MemorySafetyMargin [varchar](100) 'MemorySafetyMargin'
,MemoryThreshold [varchar](100) 'MemoryThreshold'
,WorkingSetMaximum [varchar](100) 'WorkingSetMaximum'
, WorkingSetMinimum [varchar](100) 'WorkingSetMinimum'
)


EXEC sp_xml_removedocument @hDoc


drop table #XMLwithOpenXML